# coding=utf-8
from sqlalchemy import func

from common import slave
from common.recharge.model import RechargeOrder, RECHARGE_ORDER_STATUS
from common.utils import track_logging
from common.utils.decorator import slave_wrapper

_LOGGER = track_logging.getLogger(__name__)


@slave_wrapper
def get_recharge_hourly_data(started_at, ended_at, mch_id=None, pay_type=None):
    query = slave.session.query(
        func.date(func.convert_tz(RechargeOrder.created_at, '+00:00', '+08:00')),
        func.hour(func.convert_tz(RechargeOrder.created_at, '+00:00', '+08:00')),
        RechargeOrder.status,
        func.count(RechargeOrder.id),
        func.sum(RechargeOrder.amount)). \
        filter(RechargeOrder.created_at >= started_at).filter(RechargeOrder.created_at < ended_at)
    junction = slave.and_
    filters = []
    if mch_id and mch_id != 0:
        filters.append(RechargeOrder.mch_id == mch_id)
    if pay_type:
        filters.append(RechargeOrder.pay_type == pay_type)
    query = query.filter(junction(*filters))
    items = query.group_by(
        func.date(func.convert_tz(RechargeOrder.created_at, '+00:00', '+08:00')),
        func.hour(func.convert_tz(RechargeOrder.created_at, '+00:00', '+08:00')),
        RechargeOrder.status)
    return items


@slave_wrapper
def get_recharge_daily_data(started_at, ended_at, mch_id=None, pay_type=None):
    query = slave.session.query(
        func.date(func.convert_tz(RechargeOrder.created_at, '+00:00', '+08:00')),
        RechargeOrder.status,
        func.count(RechargeOrder.id),
        func.sum(RechargeOrder.amount)). \
        filter(RechargeOrder.created_at >= started_at).filter(RechargeOrder.created_at < ended_at)
    junction = slave.and_
    filters = []
    if mch_id and mch_id != 0:
        filters.append(RechargeOrder.mch_id == mch_id)
    if pay_type:
        filters.append(RechargeOrder.pay_type == pay_type)
    query = query.filter(junction(*filters))
    items = query.group_by(
        func.date(func.convert_tz(RechargeOrder.created_at, '+00:00', '+08:00')),
        RechargeOrder.status)
    return items


@slave_wrapper
def get_recharge_effi_hourly_data(mch_id, started_at, ended_at, pay_type=None):
    """ 充值效率数据[每时] """
    query = slave.session.query(
        func.date(func.convert_tz(RechargeOrder.created_at, '+00:00', '+08:00')),
        func.hour(func.convert_tz(RechargeOrder.created_at, '+00:00', '+08:00')),
        func.isnull(RechargeOrder.detail),  # 有描述订单为人工处理的
        func.count(RechargeOrder.id),
        func.sum(func.time_to_sec(func.timediff(RechargeOrder.updated_at, RechargeOrder.created_at)))). \
        filter(RechargeOrder.status == RECHARGE_ORDER_STATUS.DONE). \
        filter(RechargeOrder.created_at >= started_at).filter(RechargeOrder.created_at < ended_at)
    junction = slave.and_
    filters = []
    if mch_id and mch_id != 0:
        filters.append(RechargeOrder.mch_id == mch_id)
    if pay_type:
        filters.append(RechargeOrder.pay_type == pay_type)
    query = query.filter(junction(*filters))
    items = query.group_by(
        func.date(func.convert_tz(RechargeOrder.created_at, '+00:00', '+08:00')),
        func.hour(func.convert_tz(RechargeOrder.created_at, '+00:00', '+08:00')),
        func.isnull(RechargeOrder.detail))
    return items


@slave_wrapper
def get_recharge_effi_daily_data(mch_id, started_at, ended_at, pay_type=None):
    """ 充值效率数据[每天] """
    query = slave.session.query(
        func.date(func.convert_tz(RechargeOrder.created_at, '+00:00', '+08:00')),
        func.isnull(RechargeOrder.detail),  # 有描述订单为人工处理的
        func.count(RechargeOrder.id),
        func.sum(func.time_to_sec(func.timediff(RechargeOrder.updated_at, RechargeOrder.created_at)))). \
        filter(RechargeOrder.status == RECHARGE_ORDER_STATUS.DONE). \
        filter(RechargeOrder.created_at >= started_at).filter(RechargeOrder.created_at < ended_at)
    junction = slave.and_
    filters = []
    if mch_id and mch_id != 0:
        filters.append(RechargeOrder.mch_id == mch_id)
    if pay_type:
        filters.append(RechargeOrder.pay_type == pay_type)
    query = query.filter(junction(*filters))
    items = query.group_by(
        func.date(func.convert_tz(RechargeOrder.created_at, '+00:00', '+08:00')),
        func.isnull(RechargeOrder.detail))
    return items
